package com.nf.xiaomi.dao.impl;

import com.nf.dbutils.SqlExecutor;
import com.nf.dbutils.handlers.BeanHandler;
import com.nf.dbutils.handlers.BeanListHandler;
import com.nf.dbutils.handlers.ScalarHandler;
import com.nf.xiaomi.dao.ProductDao;
import com.nf.xiaomi.entity.ProductEntity;
import com.nf.xiaomi.util.SqlExecutorUtils;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    SqlExecutor executor = SqlExecutorUtils.getExecutor();

    public List<ProductEntity> getAll(Integer categoryID) {
        String sql = "select product_id,product_picture,product_name,product_price," +
                "product_date,product_amount,p.nstate,c.category_name from product p JOIN" +
                " category c on p.category_id=c.category_id where p.category_id = ?";
        return executor.query(sql, new BeanListHandler<>(ProductEntity.class), categoryID);
    }

    @Override
    public List<ProductEntity> getAll() {
        String sql = "select p.product_id,p.product_name,c.category_name,p.product_picture,p.product_price," +
                "p.product_describe,p.product_date,p.nstate from product p JOIN category c\n" +
                "ON p.category_id=c.category_id ";
        return executor.query(sql, new BeanListHandler<>(ProductEntity.class));
    }

    public List<ProductEntity> getAll(String name) {
        String sql = "select product_id,product_name,category_name,product_picture,product_price," +
                "product_describe,product_date,product_amount,p.nstate from product p JOIN category c\n" +
                "ON p.category_id=c.category_id where product_name like ?";
        BeanListHandler<ProductEntity> listHandler = new BeanListHandler<>(ProductEntity.class);
        return executor.query(sql, listHandler, "%"+name+"%");
    }

    //这个暂时别动
    @Override
    public List<ProductEntity> listByCid(int cid) {
        SqlExecutor executor = SqlExecutorUtils.getExecutor();

        String sql="select id,name,price from product where category_id =?";
        BeanListHandler<ProductEntity> handler=new BeanListHandler<>(ProductEntity.class);
        return executor.query(sql, handler,cid);
    }

    @Override
    public BigInteger insert(ProductEntity entity) {
        String sql = "insert into product(category_id,product_name,product_price,product_picture,product_describe,product_date,nstate)" +
                "values(?,?,?,?,?,?,?)";
        return executor.insert(sql,new ScalarHandler<>(),entity.getCategoryID(),entity.getProductName(),entity.getProductPrice(),entity.getProductPictureList(),entity.getProductDescribe(),entity.getProductDate(),entity.getState());
    }

    @Override
    public Integer update(ProductEntity entity) {
        String sql="update product set product_name=?,category_id=(select category_id from category where category_name=?),product_price=?,product_picture=?,product_describe=?,product_date=?,nstate=? where product_id = ?";
        return executor.update(sql,entity.getProductName(),entity.getCategoryName(),entity.getProductPrice(),entity.getProductPictureList(),entity.getProductDescribe(),entity.getProductDate(),entity.getState(),entity.getProductID());
    }

    @Override
    public Integer deleteById(Integer id) {
        String sql = "delete from product where product_id = ?";
        return executor.update(sql, id);
    }

    @Override
    public ProductEntity getProductByPid(Integer pid) {
        String sql = "select pd.product_detail_id,p.product_id,p.category_id,p.product_name,a.attribute_list,pd.product_detail_price,pd.product_detail_amount,p.product_picture,p.product_price,p.product_picture,p.product_describe,p.product_date,p.nstate from product_detail pd " +
                "inner join product p on pd.product_id = p.product_id " +
                "inner join attribute a on a.attribute_id = pd.attribute_id\n" +
                "where p.product_id = ?";
        BeanHandler<ProductEntity> listHandler = new BeanHandler<>(ProductEntity.class);
        return executor.query(sql, listHandler, pid);
    }

    @Override
    public List<ProductEntity> getProductAndAttributeList(Object... params) {
        if(params.length<=0){
            return new ArrayList<ProductEntity>();
        }
        String sql="select pd.product_detail_id,p.product_id,p.category_id,p.product_name,a.attribute_list,pd.product_detail_price,pd.product_detail_amount,pd.product_picture_list,p.product_price,p.product_picture,p.product_describe,p.product_date,p.nstate from product_detail pd " +
                "inner join product p on pd.product_id = p.product_id " +
                "inner join attribute a on a.attribute_id = pd.attribute_id  where pd.product_detail_id in(";

        for (int i = 0; i < params.length; i++) {
            sql+="?";
            if(i!=params.length-1){
                sql+=",";
            }
        }
        sql+=")";
        return executor.query(sql,new BeanListHandler<>(ProductEntity.class),params);
    }

    @Override
    public Integer setProductAmount(Integer productID, Integer amount) {
        String sql="update product set product_amount=? where product_id=?";
        return executor.update(sql,amount,productID);
    }
}

